using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using ADEVSO.Data.Sql;

namespace ADEVSO.Data.SqlClient
{
    /// <summary>
    /// SqlServer Class
    /// </summary>
    public class SqlServer : Connection
    {
        #region Variables

        static System.Collections.Hashtable Commands = new System.Collections.Hashtable();

        #endregion

        #region Constructors

        /// <summary>
        /// Constructor
        /// </summary>
        public SqlServer()
        {
            InnerConnection = new System.Data.SqlClient.SqlConnection();
        }

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="connectionString"></param>
        public SqlServer(String connectionString)
        {
            InnerConnection = new System.Data.SqlClient.SqlConnection(connectionString);
        }

        #endregion

        #region Methods

        /// <summary>
        /// Load Command Parameters
        /// </summary>
        /// <param name="command"></param>
        /// <param name="args"></param>
        protected override void LoadParameters(IDbCommand command, Object[] args)
        {
            for (int i = 1; i < command.Parameters.Count; i++)
            {
                SqlParameter parameter = command.Parameters[i] as SqlParameter;
                if (i <= args.Length)
                    parameter.Value = args[i - 1];
                else
                    parameter.Value = null;
            }
        }

        /// <summary>
        /// Create a Command
        /// </summary>
        /// <param name="storedProcedure"></param>
        /// <returns></returns>
        protected override IDbCommand CreateCommand(String storedProcedure)
        {
            SqlCommand command;
            if (!InTransaction)
            {
                command = new SqlCommand(storedProcedure, InnerConnection as System.Data.SqlClient.SqlConnection);
            }
            else
                command = new SqlCommand(storedProcedure, InnerConnection as System.Data.SqlClient.SqlConnection, Transaction as SqlTransaction);
            return command;
        }

        /// <summary>
        /// Create a Command
        /// </summary>
        /// <param name="storedProcedure"></param>
        /// <returns></returns>
        protected override IDbCommand Command(String storedProcedure)
        {
            Commands = new System.Collections.Hashtable();
            SqlCommand command;
            if (Commands.Contains(storedProcedure))
                command = Commands[storedProcedure] as SqlCommand;
            else
            {
                try
                {
                    Open();
                    command = CreateCommand(storedProcedure) as SqlCommand;
                    command.CommandType = CommandType.StoredProcedure;
                    SqlCommandBuilder.DeriveParameters(command);
                    Commands.Add(storedProcedure, command);
                }
                catch (Exception e)
                {
                    throw e;
                }
                finally
                {
                    Close();
                }
            }
            command.Connection = InnerConnection as System.Data.SqlClient.SqlConnection;
            command.Transaction = Transaction as SqlTransaction;
            return command as IDbCommand;
        }

        /// <summary>
        /// Create Database Connection
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        protected override IDbConnection CreateConnection(String connectionString)
        {
            return new System.Data.SqlClient.SqlConnection(connectionString) as IDbConnection;
        }

        /// <summary>
        /// Create Database Data Adapter
        /// </summary>
        /// <param name="storedProcedure"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        protected override IDataAdapter CreateDataAdapter(String storeProcedure, params Object[] args)
        {
            SqlDataAdapter adapter = new SqlDataAdapter(Command(storeProcedure) as SqlCommand);
            if (args.Length != 0)
                LoadParameters(adapter.SelectCommand, args);
            return adapter as IDataAdapter;
        }

        /// <summary>
        /// Save image Into Sql Server Table
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="idColumnName"></param>
        /// <param name="imageColumnName"></param>
        /// <param name="id"></param>
        /// <param name="image"></param>
        /// <returns></returns>
        public override int SaveImage(String tableName, String idColumnName, String imageColumnName, long id, byte[] image)
        {
            int result = 0;
            try
            {
                BeginTransaction();
                String query = String.Format("UPDATE {0} SET {1} = @{1} WHERE {2} = @{2}", tableName, imageColumnName, idColumnName);
                SqlCommand command = CreateCommand(query) as SqlCommand;
                SqlParameter parameter = new SqlParameter("@" + idColumnName, SqlDbType.BigInt);
                parameter.Value = id;
                command.Parameters.Add(parameter);
                parameter = new SqlParameter("@" + imageColumnName, SqlDbType.Image);
                parameter.Value = image;
                command.Parameters.Add(parameter);
                result = command.ExecuteNonQuery();
                CommitTransaction();
            }
            catch (Exception e)
            {
                AbortTransaction();
                throw e;
            }
            return result;
        }

        /// <summary>
        /// Get Databases from Server
        /// </summary>
        /// <returns></returns>
        public override List<String> GetDatabases()
        {
            List<String> data = new List<String>();
            try
            {
                BeginTransaction();
                String query = @"SELECT name As DatabaseName
                                FROM master.dbo.sysdatabases";
                SqlCommand command = CreateCommand(query) as SqlCommand;
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                        data.Add(reader["DatabaseName"].ToString());
                    reader.Close();
                }
                CommitTransaction();
            }
            catch (Exception e)
            {
                AbortTransaction();
                throw e;
            }
            return data;
        }

        /// <summary>
        /// Get Database Tables
        /// </summary>
        /// <returns></returns>
        public override List<String> GetTables()
        {
            List<String> data = new List<String>();
            try
            {
                BeginTransaction();
                String query = @"SELECT table_name AS Name
                                FROM INFORMATION_SCHEMA.Tables
                                WHERE table_type = 'BASE TABLE'";
                SqlCommand command = CreateCommand(query) as SqlCommand;
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                        data.Add(reader["Name"].ToString());
                    reader.Close();
                }
                CommitTransaction();
            }
            catch (Exception e)
            {
                AbortTransaction();
                throw e;
            }
            return data;
        }

        /// <summary>
        /// Get Database Table Primary Key
        /// </summary>
        /// <param name="Table"></param>
        /// <returns></returns>
        public override List<PrimaryKey> GetTablePKs(String Table)
        {
            List<PrimaryKey> data = new List<PrimaryKey>();
            try
            {
                BeginTransaction();
                String query = String.Format(@"SELECT pk.name AS PrimaryKeyName, o.name AS tableName, c.name AS ColumnName
                                            FROM sysindexes i
	                                            JOIN sysobjects o ON i.id = o.id
	                                            JOIN sysobjects pk ON i.name = pk.name
		                                            AND pk.parent_obj = i.id
		                                            AND pk.xtype = 'PK'
	                                            JOIN sysindexkeys ik on i.id = ik.id
		                                            AND i.indid = ik.indid
	                                            JOIN syscolumns c ON ik.id = c.id
		                                            AND ik.colid = c.colid
                                            WHERE o.name = '{0}'
                                            ORDER BY o.name", Table);
                SqlCommand command = CreateCommand(query) as SqlCommand;
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        PrimaryKey row = new PrimaryKey();
                        row.PrimaryKeyName = reader["PrimaryKeyName"].ToString();
                        row.TableName = reader["tableName"].ToString();
                        row.ColumnName = reader["ColumnName"].ToString();
                        data.Add(row);
                    }
                    reader.Close();
                }
                CommitTransaction();
            }
            catch (Exception e)
            {
                AbortTransaction();
                throw e;
            }
            return data;
        }

        /// <summary>
        /// Get Database Table Columns
        /// </summary>
        /// <param name="Table"></param>
        /// <returns></returns>
        public override List<Column> GetTableColumns(String Table)
        {
            List<Column> data = new List<Column>();
            try
            {
                List<PrimaryKey> PKs = GetTablePKs(Table);
                List<ForeignKey> FKs = GetTableFKs(Table);
                List<Identity> Identities = GetTableIdentities(Table);
                BeginTransaction();
                String query = String.Format(@"SELECT Table_Name, Column_Name, Data_Type, Character_Maximum_Length, Is_Nullable, Column_Default
                                                FROM Information_Schema.Columns
                                                WHERE Table_Name = '{0}'
                                                ORDER BY Table_Name, Ordinal_Position", Table);
                SqlCommand command = CreateCommand(query) as SqlCommand;
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Column row = new Column();
                        row.TableName = reader["Table_Name"].ToString();
                        row.ColumnName = reader["Column_Name"].ToString();
                        row.DataType = reader["Data_Type"].ToString();
                        row.MaxLength = reader["Character_Maximum_Length"] != System.DBNull.Value ? int.Parse(reader["Character_Maximum_Length"].ToString()) : 0;
                        row.AllowNulls = reader["Is_Nullable"].Equals("YES") ? true : false;
                        row.Value = reader["Column_Default"] != System.DBNull.Value ? reader["Column_Default"].ToString() : null;
                        row.IsPrimaryKey = PKs.Contains(row.ColumnName) ? true : false;
                        row.IsForeignKey = FKs.Contains(row.ColumnName) ? true : false;
                        row.IsIdentity = Identities.Contains(row.ColumnName) ? true : false;
                        data.Add(row);
                    }
                    reader.Close();
                }
                CommitTransaction();
            }
            catch
            {
                AbortTransaction();
            }
            return data;
        }

        /// <summary>
        /// Get Database Table Foreign Keys
        /// </summary>
        /// <returns></returns>
        public override List<ForeignKey> GetTableFKs(String Table)
        {
            List<ForeignKey> data = new List<ForeignKey>();
            try
            {
                BeginTransaction();
                String query = String.Format(@"SELECT OBJECT_NAME(sfk.constid) AS ForeignKey, 
	                                            sof.name AS ForeignTableName,
	                                            sor.name AS ReferencedTableName,
	                                            scf.name AS ForeignColumnName,
	                                            scr.name AS ReferencedColumnName
                                            FROM dbo.sysforeignkeys sfk
	                                            INNER JOIN dbo.sysobjects sof
		                                            ON sfk.fkeyid = sof.id
	                                            INNER JOIN dbo.sysobjects sor
		                                            ON sfk.rkeyid = sor.id
	                                            INNER JOIN dbo.syscolumns scf
		                                            ON sfk.fkey = scf.colid AND sof.id = scf.id
	                                            INNER JOIN dbo.syscolumns scr
		                                            ON sfk.rkey = scr.colid AND sor.id = scr.id
                                            WHERE sof.name = '{0}'
                                            ORDER BY sfk.constid, sof.name, sor.name", Table);
                SqlCommand command = CreateCommand(query) as SqlCommand;
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        ForeignKey row = new ForeignKey();
                        row.ForeingKeyName = reader["ForeignKey"].ToString();
                        row.TableName = reader["ForeignTableName"].ToString();
                        row.ReferencedTableName = reader["ReferencedTableName"].ToString();
                        row.ColumnName = reader["ForeignColumnName"].ToString();
                        row.ReferencedColumnName = reader["ReferencedColumnName"].ToString();
                        data.Add(row);
                    }
                    reader.Close();
                }
                CommitTransaction();
            }
            catch
            {
                AbortTransaction();
            }
            return data;
        }

        /// <summary>
        /// Get Database Table Foreign Keys Names
        /// </summary>
        /// <returns></returns>
        public List<String> GetTableFKsNames(String Table)
        {
            return GetTableFKs(Table).ToStringArray();
        }

        /// <summary>
        /// Get Database Table Identities
        /// </summary>
        /// <returns></returns>
        public override List<Identity> GetTableIdentities(String Table)
        {
            List<Identity> data = new List<Identity>();
            try
            {
                BeginTransaction();
                String query = String.Format(@"SELECT t.name AS tableName, c.name AS ColumnName
                                            FROM sys.columns c
	                                            INNER JOIN sys.tables t 
	                                            ON t.object_id = c.object_id
                                            WHERE c.is_identity = 1
                                            AND t.name = '{0}'", Table);
                SqlCommand command = CreateCommand(query) as SqlCommand;
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Identity identity = new Identity();
                        identity.TableName = reader["tableName"].ToString();
                        identity.ColumnName = reader["ColumnName"].ToString();
                        data.Add(identity);
                    }
                    reader.Close();
                }
                CommitTransaction();
            }
            catch
            {
                AbortTransaction();
            }
            return data;
        }

        #endregion
    }
}